package com.schx.budget.mapper

import com.schx.budget.pojo.Budget
import com.schx.budget.pojo.BudgetTarget
import org.apache.ibatis.annotations.Delete
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Select
import org.apache.ibatis.annotations.Update
import java.math.BigDecimal

interface BudgetMapper {

    /**
     * 实际费用
     */
    @Select("<script>" +
            "select sum(actualAmount) actualAmount,\n" +
            "       sum(nowMonthIncome) nowMonthIncome,\n" +
            "       project,\n" +
            "    sum(actualAmount1) lastIncome,\n" +
            "       sum(nowMonthIncome1) lastMonthIncome\n" +
            "  from (select *\n" +
            "          from (select sum(a.amount) actualAmount,\n" +
            "                       0 nowMonthIncome,\n" +
            "                       a.project,\n" +
            "                       0 actualAmount1,\n" +
            "                       0 nowMonthIncome1\n" +
            "                  from (select amount,\n" +
            "                               case\n" +
            "                                 when subtitle_name like '%工资%' or\n" +
            "                                      subtitle_name like '费用项目%保险%' or\n" +
            "                                      subtitle_name like '%年金%' or\n" +
            "                                      subtitle_name like '%公积金%' then\n" +
            "                                  '费用一'\n" +
            "                                 when subtitle_name like '%福利费%' or\n" +
            "                                      subtitle_name like '%工会%' or\n" +
            "                                      subtitle_name like '%教育%' then\n" +
            "                                  '费用三'\n" +
            "                                 when subtitle_name like '%费用项目%' and\n" +
            "                                      subtitle_name not like '费用项目%保险%' then\n" +
            "                                  '费用二'\n" +
            "                                 else\n" +
            "                                  '其他'\n" +
            "                               end project\n" +
            "                          from af_cost_qs_depa\n" +
            "                          where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>\n" +
            "                           period_name like concat(#{year}, '%')\n" +
            "                           and subtitle_name != '缺省') a\n" +
            "                 group by project) s\n" +
            "         where s.actualAmount > 0\n" +
            "        union all\n" +
            "        select *\n" +
            "          from (select 0 actualAmount,\n" +
            "                       sum(a.amount) nowMonthIncome,\n" +
            "                       a.project,\n" +
            "                       0 actualAmount1,0 nowMonthIncome1\n" +
            "                  from (select amount,\n" +
            "                               case\n" +
            "                                 when subtitle_name like '%工资%' or\n" +
            "                                      subtitle_name like '费用项目%保险%' or\n" +
            "                                      subtitle_name like '%年金%' or\n" +
            "                                      subtitle_name like '%公积金%' then\n" +
            "                                  '费用一'\n" +
            "                                 when subtitle_name like '%福利费%' or\n" +
            "                                      subtitle_name like '%工会%' or\n" +
            "                                      subtitle_name like '%教育%' then\n" +
            "                                  '费用三'\n" +
            "                                 when subtitle_name like '%费用项目%' and\n" +
            "                                      subtitle_name not like '费用项目%保险%' then\n" +
            "                                  '费用二'\n" +
            "                                 else\n" +
            "                                  '其他'\n" +
            "                               end project\n" +
            "                          from af_cost_qs_depa\n" +
            "                          where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>\n" +
            "                           period_name like concat(#{month}, '%')\n" +
            "                           and subtitle_name != '缺省') a\n" +
            "                 group by a.project) s\n" +
            "         where s.nowMonthIncome > 0\n" +
            "         union all --本月之前的所有\n" +
            "         select *\n" +
            "          from (select 0 actualAmount,0 nowMonthIncome,a.project,\n" +
            "                       sum(a.amount) actualAmount1,                       \n" +
            "                       0  nowMonthIncome1\n" +
            "                  from (select 0,0,\n" +
            "                               case\n" +
            "                                 when subtitle_name like '%工资%' or\n" +
            "                                      subtitle_name like '费用项目%保险%' or\n" +
            "                                      subtitle_name like '%年金%' or\n" +
            "                                      subtitle_name like '%公积金%' then\n" +
            "                                  '费用一'\n" +
            "                                 when subtitle_name like '%福利费%' or\n" +
            "                                      subtitle_name like '%工会%' or\n" +
            "                                      subtitle_name like '%教育%' then\n" +
            "                                  '费用三'\n" +
            "                                 when subtitle_name like '%费用项目%' and\n" +
            "                                      subtitle_name not like '费用项目%保险%' then\n" +
            "                                  '费用二'\n" +
            "                                 else\n" +
            "                                  '其他'\n" +
            "                               end project,amount,0\n" +
            "                          from af_cost_qs_depa\n" +
            "                          where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>\n" +
            "                           period_name &lt; to_char(trunc(sysdate,'mm'),'yyyy-mm')\n" +
            "                           and period_name &gt;= to_char(trunc(sysdate,'yyyy'),'yyyy-mm')\n" +
            "                           and subtitle_name != '缺省') a\n" +
            "                 group by a.project) s WHERE s.actualAmount1 &gt; 0\n" +
            "       union all\n" +
            "        select *\n" +
            "          from (select 0 actualAmount,0 nowMonthIncome,a.project,\n" +
            "                       0 actualAmount1,                       \n" +
            "                       sum(a.amount) nowMonthIncome1\n" +
            "                  from (select 0,0,\n" +
            "                               case\n" +
            "                                 when subtitle_name like '%工资%' or\n" +
            "                                      subtitle_name like '费用项目%保险%' or\n" +
            "                                      subtitle_name like '%年金%' or\n" +
            "                                      subtitle_name like '%公积金%' then\n" +
            "                                  '费用一'\n" +
            "                                 when subtitle_name like '%福利费%' or\n" +
            "                                      subtitle_name like '%工会%' or\n" +
            "                                      subtitle_name like '%教育%' then\n" +
            "                                  '费用三'\n" +
            "                                 when subtitle_name like '%费用项目%' and\n" +
            "                                      subtitle_name not like '费用项目%保险%' then\n" +
            "                                  '费用二'\n" +
            "                                 else\n" +
            "                                  '其他'\n" +
            "                               end project,0,amount\n" +
            "                          from af_cost_qs_depa\n" +
            "                          where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>\n" +
            "                           period_name &lt; to_char(trunc(sysdate,'mm'),'yyyy-mm')\n" +
            "                           and period_name &gt;= to_char(trunc(add_months(sysdate,-1),'mm'),'yyyy-mm')\n" +
            "                           and subtitle_name != '缺省') a\n" +
            "                 group by a.project) s WHERE s.nowmonthincome1 &gt; 0\n" +
            "         )\n" +
            " group by project\n" +
            "</script>")
    fun getCostList(year: String, month: String, orgid: String): List<Budget>
//    @Select("<script> " +
//            "select sum(actualAmount)actualAmount, sum(nowMonthIncome) nowMonthIncome,project from (select * from  (    \n" +
//            "        select sum(a.amount) actualAmount,0 nowMonthIncome, a.project from (\n" +
//            "        select amount, case \n" +
//            "        when subtitle_name like '%工资%' or subtitle_name like '费用项目%保险%' or subtitle_name like '%年金%' or subtitle_name like '%公积金%' then '费用一' \n" +
//            "        when subtitle_name like '%福利费%' or subtitle_name like '%工会%' or subtitle_name like '%教育%' then '费用三' \n" +
//            "        when subtitle_name like '%费用项目%' and subtitle_name not like '费用项目%保险%' then '费用二' \n" +
//            "        else '其他' end project\n" +
//            "        from af_cost_qs_depa\n" +
//            "        where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>\n" +
//            "        period_name like concat(#{year}, '%')\n" +
//            "        and subtitle_name !='缺省')a\n" +
//            "        group by project) s where s.actualAmount &gt;0 \n" +
//            "        union all \n" +
//            "        select * from  (    \n" +
//            "        select 0 actualAmount,sum(a.amount) nowMonthIncome, a.project from (\n" +
//            "        select amount, case \n" +
//            "        when subtitle_name like '%工资%' or subtitle_name like '费用项目%保险%' or subtitle_name like '%年金%' or subtitle_name like '%公积金%' then '费用一' \n" +
//            "        when subtitle_name like '%福利费%' or subtitle_name like '%工会%' or subtitle_name like '%教育%' then '费用三' \n" +
//            "        when subtitle_name like '%费用项目%' and subtitle_name not like '费用项目%保险%' then '费用二' \n" +
//            "        else '其他' end project\n" +
//            "        from af_cost_qs_depa\n" +
//            "        where <if test='orgid != \"\" '> segment1 = #{orgid} and  </if>\n" +
//            "        period_name like concat(#{month}, '%')\n" +
//            "        and subtitle_name !='缺省')a         group by a.project ) s where s.nowMonthIncome &gt; 0    \n" +
//            "        )\n" +
//            "        group by project" +
//            "</script>\n")
//    fun getCostList(year: String, month: String, orgid: String): List<Budget>

    @Select("<script>SELECT\n" +
            "    SUM(amount_goal) budgetAmount,\n" +
            "    CASE\n" +
            "        WHEN type = '31' THEN\n" +
            "            '费用一'\n" +
            "        WHEN type = '32' THEN\n" +
            "            '费用二'\n" +
            "        ELSE\n" +
            "            '费用三'\n" +
            "    END project \n" +
            "FROM\n" +
            "    budget_target\n" +
            "WHERE\n" +
            "    <if test='orgid == \"\"'> orgid = '1001' and </if>" +
            "    <if test='orgid != \"\"'> orgid = #{orgid} and </if>\n" +
            "    type LIKE '3%'\n" +
            "GROUP BY\n" +
            "    type</script>")
    fun getCostBudget(orgid: String): List<Budget>

    /**
     * 费用明细
     */
    @Select("<script>" +
            "SELECT\n" +
            "                project,\n" +
            "                sum(nowmonthincome) nowmonthincome,\n" +
            "                sum(actualamount) actualamount,\n" +
            "                sum(budgetAmount) budgetAmount,\n" +
            "                sum(actualamount1) lastIncome,\n" +
            "                sum(nowmonthincome1) lastMonthIncome   \n" +
            "            FROM\n" +
            "                (SELECT\n" +
            "                t.content project,\n" +
            "                nvl(SUM(actualamount), 0) actualamount,\n" +
            "                nvl(SUM(nowmonthincome), 0) nowmonthincome,\n" +
            "                t.amount_goal budgetAmount,\n" +
            "                nvl(SUM(actualamount1), 0) actualamount1,\n" +
            "                nvl(SUM(nowmonthincome1), 0) nowmonthincome1\n" +
            "            FROM\n" +
            "                (\n" +
            "                    SELECT\n" +
            "                        a.project,\n" +
            "                        SUM(amount) actualamount,\n" +
            "                        0 nowmonthincome,\n" +
            "                        0 actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                    FROM\n" +
            "                        (\n" +
            "                            SELECT\n" +
            "                                amount,\n" +
            "                                CASE\n" +
            "                                    WHEN subtitle_name LIKE '%工资%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%保险%'\n" +
            "                                         OR subtitle_name LIKE '%公积金%'\n" +
            "                                         OR subtitle_name LIKE '%年金%' THEN\n" +
            "                                        '工资及社保、年金'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%资料费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%电话费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
            "                                        '办公费'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
            "                                        '邮递费+运输费'\n" +
            "                                    ELSE\n" +
            "                                        substr(subtitle_name, 6)\n" +
            "                                END project,\n" +
            "                        0 nowmonthincome,\n" +
            "                        0 actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                            FROM\n" +
            "                                af_cost_qs_depa\n" +
            "                            WHERE\n" +
            "                                <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
            "                                period_name LIKE concat(#{year}, '%')\n" +
            "                                AND subtitle_name LIKE '%费用项目%'\n" +
            "                        ) a\n" +
            "                    GROUP BY\n" +
            "                        a.project\n" +
            "                    UNION ALL\n" +
            "                    SELECT\n" +
            "                        project,\n" +
            "                        0 actualamount,\n" +
            "                        SUM(amount) nowmonthincome,\n" +
            "                        0 actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                    FROM\n" +
            "                        (\n" +
            "                            SELECT\n" +
            "                                amount,\n" +
            "                                CASE\n" +
            "                                    WHEN subtitle_name LIKE '%工资%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%保险%'\n" +
            "                                         OR subtitle_name LIKE '%公积金%'\n" +
            "                                         OR subtitle_name LIKE '%年金%' THEN\n" +
            "                                        '工资及社保、年金'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%资料费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%电话费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
            "                                        '办公费'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
            "                                        '邮递费+运输费'\n" +
            "                                    ELSE\n" +
            "                                        substr(subtitle_name, 6)\n" +
            "                                END project,\n" +
            "                                0 actualamount1,\n" +
            "                                0 nowmonthincome1                               \n" +
            "                            FROM\n" +
            "                                af_cost_qs_depa\n" +
            "                            WHERE\n" +
            "                                <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
            "                                period_name LIKE concat(#{month}, '%')\n" +
            "                                AND subtitle_name LIKE '%费用项目%'\n" +
            "                        ) a\n" +
            "                    GROUP BY\n" +
            "                        a.project\n" +
            "\n" +
            "                union all\n" +
            "                  SELECT\n" +
            "                        project,\n" +
            "                        0 actualamount,\n" +
            "                        0 nowmonthincome,\n" +
            "                        SUM(amount) actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                    FROM\n" +
            "                        (\n" +
            "                            SELECT\n" +
            "                                amount,\n" +
            "                                CASE\n" +
            "                                    WHEN subtitle_name LIKE '%工资%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%保险%'\n" +
            "                                         OR subtitle_name LIKE '%公积金%'\n" +
            "                                         OR subtitle_name LIKE '%年金%' THEN\n" +
            "                                        '工资及社保、年金'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%资料费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%电话费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
            "                                        '办公费'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
            "                                        '邮递费+运输费'\n" +
            "                                    ELSE\n" +
            "                                        substr(subtitle_name, 6)\n" +
            "                                END project,\n" +
            "                                0 actualamount1,\n" +
            "                                0 nowmonthincome1                               \n" +
            "                            FROM\n" +
            "                                af_cost_qs_depa\n" +
            "                            WHERE\n" +
            "                            <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
            "                            period_name &lt; to_char(trunc(sysdate,'mm'),'yyyy-mm')\n" +
            "                            and period_name &gt;= to_char(trunc(sysdate,'yyyy'),'yyyy-mm')\n" +
            "                                AND subtitle_name LIKE '%费用项目%'\n" +
            "                        ) a\n" +
            "                    GROUP BY\n" +
            "                        a.project\n" +
            "                                 union all\n" +
            "                  SELECT\n" +
            "                        project,\n" +
            "                        0 actualamount,\n" +
            "                        0 nowmonthincome,\n" +
            "                        0 actualamount1,\n" +
            "                        SUM(amount) nowmonthincome1\n" +
            "                    FROM\n" +
            "                        (\n" +
            "                            SELECT\n" +
            "                                amount,\n" +
            "                                CASE\n" +
            "                                    WHEN subtitle_name LIKE '%工资%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%保险%'\n" +
            "                                         OR subtitle_name LIKE '%公积金%'\n" +
            "                                         OR subtitle_name LIKE '%年金%' THEN\n" +
            "                                        '工资及社保、年金'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%资料费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%电话费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
            "                                        '办公费'\n" +
            "                                    WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
            "                                         OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
            "                                        '邮递费+运输费'\n" +
            "                                    ELSE\n" +
            "                                        substr(subtitle_name, 6)\n" +
            "                                END project,\n" +
            "                                0 actualamount1,\n" +
            "                                0 nowmonthincome1                               \n" +
            "                            FROM\n" +
            "                                af_cost_qs_depa\n" +
            "                            WHERE\n" +
            "                                 <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
            "                                 period_name &lt; to_char(trunc(sysdate,'mm'),'yyyy-mm')\n" +
            "                                 and period_name &gt;= to_char(trunc(add_months(sysdate,-1),'mm'),'yyyy-mm')\n" +
            "                                AND subtitle_name LIKE '%费用项目%'\n" +
            "                        ) a\n" +
            "                    GROUP BY\n" +
            "                        a.project\n" +
            "                \n" +
            "                ) s\n" +
            "                RIGHT JOIN budget_target t ON t.content = s.project\n" +
            "            WHERE\n" +
            "                t.year = #{year}\n" +
        "                <if test='orgid != \"\"'> and t.orgid = #{orgid}  </if>" +
        "                <if test='orgid == \"\"'> and t.orgid = '1001'  </if>\n" +
            "                AND t.type = #{type}\n" +
            "            GROUP BY\n" +
            "                t.content,t.amount_goal \n" +
            "            ORDER BY actualamount desc )\n" +
            "            GROUP BY\n" +
            "                project \n" +
            "            order by budgetAmount desc" +
            "</script>")
    fun getCostDetail(year: String, month: String, type: String, orgid: String): List<Budget>
//    @Select("<script>" +
//            "SELECT\n" +
//            "    project,\n" +
//            "    sum(nowmonthincome) nowmonthincome,\n" +
//            "    sum(actualamount) actualamount,\n" +
//            "    sum(budgetAmount) budgetAmount\n" +
//            "FROM\n" +
//            "    (SELECT\n" +
//            "    t.content project,\n" +
//            "    nvl(SUM(actualamount), 0) actualamount,\n" +
//            "    nvl(SUM(nowmonthincome), 0) nowmonthincome,\n" +
//            "    t.amount_goal budgetAmount\n" +
//            "FROM\n" +
//            "    (\n" +
//            "        SELECT\n" +
//            "            a.project,\n" +
//            "            SUM(amount) actualamount,\n" +
//            "            0 nowmonthincome\n" +
//            "        FROM\n" +
//            "            (\n" +
//            "                SELECT\n" +
//            "                    amount,\n" +
//            "                    CASE\n" +
//            "                        WHEN subtitle_name LIKE '%工资%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%保险%'\n" +
//            "                             OR subtitle_name LIKE '%公积金%'\n" +
//            "                             OR subtitle_name LIKE '%年金%' THEN\n" +
//            "                            '工资及社保、年金'\n" +
//            "                        WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%资料费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%电话费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
//            "                            '办公费'\n" +
//            "                        WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
//            "                            '邮递费+运输费'\n" +
//            "                        ELSE\n" +
//            "                            substr(subtitle_name, 6)\n" +
//            "                    END project\n" +
//            "                FROM\n" +
//            "                    af_cost_qs_depa\n" +
//            "                WHERE\n" +
//            "                    <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
//            "                    period_name LIKE concat(#{year}, '%')\n" +
//            "                    AND subtitle_name LIKE '%费用项目%'\n" +
//            "            ) a\n" +
//            "        GROUP BY\n" +
//            "            a.project\n" +
//            "        UNION ALL\n" +
//            "        SELECT\n" +
//            "            project,\n" +
//            "            0 actualamount,\n" +
//            "            SUM(amount) nowmonthincome\n" +
//            "        FROM\n" +
//            "            (\n" +
//            "                SELECT\n" +
//            "                    amount,\n" +
//            "                    CASE\n" +
//            "                        WHEN subtitle_name LIKE '%工资%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%保险%'\n" +
//            "                             OR subtitle_name LIKE '%公积金%'\n" +
//            "                             OR subtitle_name LIKE '%年金%' THEN\n" +
//            "                            '工资及社保、年金'\n" +
//            "                        WHEN subtitle_name LIKE '费用项目%网络费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%资料费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%电话费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%办公费%' THEN\n" +
//            "                            '办公费'\n" +
//            "                        WHEN subtitle_name LIKE '费用项目%邮递费%'\n" +
//            "                             OR subtitle_name LIKE '费用项目%运输费%' THEN\n" +
//            "                            '邮递费+运输费'\n" +
//            "                        ELSE\n" +
//            "                            substr(subtitle_name, 6)\n" +
//            "                    END project\n" +
//            "                FROM\n" +
//            "                    af_cost_qs_depa\n" +
//            "                WHERE\n" +
//            "                    <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
//            "                    period_name LIKE concat(#{month}, '%')\n" +
//            "                    AND subtitle_name LIKE '%费用项目%'\n" +
//            "            ) a\n" +
//            "        GROUP BY\n" +
//            "            a.project\n" +
//            "    ) s\n" +
//            "    RIGHT JOIN budget_target t ON t.content = s.project\n" +
//            "WHERE\n" +
//            "    t.year = #{year}" +
//            "    <if test='orgid != \"\"'> and t.orgid = #{orgid}  </if>" +
//            "    <if test='orgid == \"\"'> and t.orgid = '1001'  </if>\n" +
//            "    AND t.type = #{type}\n" +
//            "GROUP BY\n" +
//            "    t.content,t.amount_goal \n" +
//            "ORDER BY actualamount desc )\n" +
//            "GROUP BY\n" +
//            "    project " +
//            "order by budgetAmount desc\n" +
//            "</script>")
//    fun getCostDetail(year: String, month: String, type: String, orgid: String): List<Budget>


    /**
     * 费用各科目每月详情
     */
    @Select("<script>" +
            "SELECT\n" +
            "    month,\n" +
            "    SUM(amount) actualAmount," +
            "    segment3 project\n" +
            "FROM\n" +
            "    (\n" +
            "        SELECT\n" +
            "            amount,\n" +
            "            subtitle_name project,\n" +
            "            substr(period_name, 6, 7) month," +
            "            CASE\n" +
            "                WHEN segment3 LIKE '5501%' THEN\n" +
            "                    '销售费用'\n" +
            "                WHEN segment3 LIKE '5502%' THEN\n" +
            "                    '管理费用'\n" +
            "                ELSE\n" +
            "                    '损益类费用'\n" +
            "            END segment3 \n" +
            "        FROM\n" +
            "            af_cost_qs_depa\n" +
            "        WHERE\n" +
            "            <if test='orgid != \"\"'> segment1 = #{orgid} and </if>\n" +
            "            period_name LIKE concat(#{year}, '%')\n" +
            "            AND subtitle_name != '缺省'\n" +
            "            AND subtitle_name LIKE '%费用项目%'\n" +
            "            <if test='project==\"工资及社保、年金\"'>" +
            "                  AND ( subtitle_name LIKE '%工资%'\n" +
            "                  OR subtitle_name LIKE '费用项目%保险%'\n" +
            "                  OR subtitle_name LIKE '%公积金%'\n" +
            "                  OR subtitle_name LIKE '%年金%' )\n" +
            "            </if>" +
            "            <if test='project==\"办公费\"'>" +
            "                  AND ( subtitle_name LIKE '费用项目%网络费%'\n" +
            "                  OR subtitle_name LIKE '费用项目%资料费%'\n" +
            "                  OR subtitle_name LIKE '费用项目%电话费%'\n" +
            "                  OR subtitle_name LIKE '费用项目%办公费%' )\n" +
            "            </if>" +
            "            <if test='project==\"邮递费+运输费\"'>" +
            "                  AND ( subtitle_name LIKE '费用项目%邮递费%'\n" +
            "                  OR subtitle_name LIKE '费用项目%运输费%' )\n" +
            "            </if>" +
            "            <if test='project!=\"邮递费+运输费\" and project!=\"办公费\" and project!=\"工资及社保、年金\" '>" +
            "                  AND subtitle_name LIKE concat('%', concat(#{project},'%'))\n" +
            "            </if>" +
            "    )\n" +
            "GROUP BY month,segment3 " +
            "order by month asc" +
            "</script>")
    fun getCostDetailByMonth(year:String ,project: String, orgid: String): List<Budget>

    /**
     * 实际毛利-前面加1的表示政府毛利，不加1表示企业毛利
     */
    @Select("<script>" +
            "SELECT\n" +
            "                project,\n" +
            "                type3,\n" +
            "                nvl(SUM(budgetamount), 0) budgetamount,\n" +
            "                nvl(SUM(actualamount), 0) actualamount,\n" +
            "                nvl(SUM(nowmonthincome), 0) nowmonthincome,\n" +
            "                nvl(SUM(actualamount1), 0) lastIncome,\n" +
            "                nvl(SUM(nowmonthincome1), 0) lastMonthIncome\n" +
            "            FROM\n" +
            "                (\n" +
            "                    SELECT\n" +
            "                        t.type2 project,\n" +
            "                        t.type3,\n" +
            "                        b.amount_goal budgetamount,\n" +
            "                        SUM(g.amount) actualamount,\n" +
            "                        0 nowmonthincome,\n" +
            "                        0 actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                  AND to_char(g.done_date, 'yyyy') = #{year}\n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content \n" +
            "                    <if test='orgid != \"\"'> where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
            "                    GROUP BY\n" +
            "                        t.type2,b.amount_goal, \n" +
            "                        t.type3\n" +
            "                    UNION ALL\n" +
            "                    SELECT\n" +
            "                        t.type2 project,\n" +
            "                        t.type3,\n" +
            "                        '0',\n" +
            "                        0,\n" +
            "                        SUM(g.amount) nowmonthincome,\n" +
            "                        0,\n" +
            "                        0\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                  AND to_char(g.done_date, 'yyyy-mm') = #{month}\n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content  \n" +
            "                    <if test='orgid != \"\"'> where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
            "                    GROUP BY\n" +
            "                        t.type2,\n" +
            "                        t.type3\n" +
            "                   union all  \n" +
            "                   SELECT\n" +
            "                        t.type2 project,\n" +
            "                        t.type3,\n" +
            "                        '0',\n" +
            "                        0,\n" +
            "                        0,\n" +
            "                        SUM(g.amount),\n" +
            "                        0\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                        and to_char(g.done_date,'yyyy-mm-dd')&lt; =  to_char(trunc(sysdate,'mm'),'yyyy-mm-dd')\n" +
            "                        AND to_char(g.done_date,'yyyy-mm-dd')&gt;= to_char(trunc(sysdate,'yyyy'),'yyyy-mm-dd')\n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content  \n" +
            "                    <if test='orgid != \"\"'> where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
            "                    GROUP BY\n" +
            "                        t.type2,\n" +
            "                        t.type3 \n" +
            "                    union all                 \n" +
            "                       SELECT\n" +
            "                        t.type2 project,\n" +
            "                        t.type3,\n" +
            "                        '0',\n" +
            "                        0,\n" +
            "                        0,\n" +
            "                        0,\n" +
            "                        SUM(g.amount)\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                         and to_char(g.done_date,'yyyy-mm-dd')&lt; =  to_char(trunc(sysdate,'mm'),'yyyy-mm-dd')\n" +
            "                         and to_char(g.done_date,'yyyy-mm-dd')&gt; =  to_char(trunc(add_months(sysdate,-1),'mm'),'yyyy-mm-dd')\n" +
            "                        LEFT JOIN budget_target   b ON TRIM(t.type1) = b.content  \n" +
            "                    <if test='orgid != \"\"'> where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
            "                    GROUP BY\n" +
            "                        t.type2,\n" +
            "                        t.type3   \n" +
            "                )\n" +
            "            GROUP BY\n" +
            "                project,\n" +
            "                type3\n" +
            "            ORDER BY\n" +
            "                actualamount DESC" +
            "</script>")
    fun getIncomeList(year: String, month: String, orgid: String): List<Budget>
//    @Select("<script>SELECT\n" +
//            "    project,\n" +
//            "    type3,\n" +
//            "    nvl(SUM(budgetamount), 0) budgetamount,\n" +
//            "    nvl(SUM(actualamount), 0) actualamount,\n" +
//            "    nvl(SUM(nowmonthincome), 0) nowmonthincome\n" +
//            "FROM\n" +
//            "    (\n" +
//            "        SELECT\n" +
//            "            t.type2 project,\n" +
//            "            t.type3,\n" +
//            "            b.amount_goal budgetamount,\n" +
//            "            SUM(g.amount) actualamount,\n" +
//            "            0 nowmonthincome\n" +
//            "        FROM\n" +
//            "            budget_type                  t\n" +
//            "            LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
//            "                                                      AND to_char(g.done_date, 'yyyy') = #{year}\n" +
//            "            LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content" +
//            "        <if test='orgid != \"\"'> where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
//            "        <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
//            "        GROUP BY\n" +
//            "            t.type2,b.amount_goal, \n" +
//            "            t.type3\n" +
//            "        UNION ALL\n" +
//            "        SELECT\n" +
//            "            t.type2 project,\n" +
//            "            t.type3,\n" +
//            "            '0',\n" +
//            "            0,\n" +
//            "            SUM(g.amount) nowmonthincome\n" +
//            "        FROM\n" +
//            "            budget_type                  t\n" +
//            "            LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
//            "                                                      AND to_char(g.done_date, 'yyyy-mm') = #{month}\n" +
//            "            LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content " +
//            "            <if test='orgid != \"\"'>where b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
//            "            <if test='orgid == \"\"'> where b.orgid = '1001'  </if>" +
//            "        GROUP BY\n" +
//            "            t.type2,\n" +
//            "            t.type3\n" +
//            "    )\n" +
//            "GROUP BY\n" +
//            "    project,\n" +
//            "    type3\n" +
//            "ORDER BY\n" +
//            "    actualamount DESC</script>")
//    fun getIncomeList(year: String, month: String, orgid: String): List<Budget>

//    @Select("<script>SELECT\n" +
//            "    project,\n" +
//            "    nvl(SUM(budgetamount), 0) budgetamount,\n" +
//            "    nvl(SUM(actualamount), 0) actualamount,\n" +
//            "    nvl(SUM(nowmonthincome), 0) nowmonthincome\n" +
//            "FROM\n" +
//            "    (\n" +
//            "        SELECT\n" +
//            "            t.type1 project,\n" +
//            "            b.amount_goal budgetamount,\n" +
//            "            SUM(g.amount) actualamount,\n" +
//            "            0 nowmonthincome\n" +
//            "        FROM\n" +
//            "            budget_type                  t\n" +
//            "            LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
//            "                                                      AND to_char(g.done_date, 'yyyy') = #{year}\n" +
//            "            LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
//            "        WHERE\n" +
//            "            t.type2 = #{type} " +
//            "        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
//            "        <if test='orgid != \"\"'> and g.segment1 = #{orgid}  </if>\n" +
//            "        GROUP BY\n" +
//            "            t.type1,b.amount_goal\n" +
//            "        UNION ALL\n" +
//            "        SELECT\n" +
//            "            t.type1 project,\n" +
//            "            '0',\n" +
//            "            0,\n" +
//            "            SUM(g.amount) nowmonthincome\n" +
//            "        FROM\n" +
//            "            budget_type                  t\n" +
//            "            LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
//            "                                                      AND to_char(g.done_date, 'yyyy-mm') = #{month}\n" +
//            "            LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
//            "        WHERE\n" +
//            "            t.type2 = #{type}  " +
//            "        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
//            "        <if test='orgid != \"\"'> and g.segment1 = #{orgid}  </if>\n" +
//            "        GROUP BY\n" +
//            "            t.type1\n" +
//            "    )\n" +
//            "GROUP BY\n" +
//            "    project\n" +
//            "ORDER BY\n" +
//            "    actualamount DESC</script>")
//    fun getIncomeDetail(year: String, month: String, type: String, orgid: String): List<Budget>

    @Select("<script>" +
            "SELECT\n" +
            "                project,\n" +
            "                nvl(SUM(budgetamount), 0) budgetamount,\n" +
            "                nvl(SUM(actualamount), 0) actualamount,\n" +
            "                nvl(SUM(nowmonthincome), 0) nowmonthincome,\n" +
            "                nvl(SUM(actualamount1), 0) lastIncome,\n" +
            "                nvl(SUM(nowmonthincome1), 0) lastMonthIncome\n" +
            "            FROM\n" +
            "                (\n" +
            "                    SELECT\n" +
            "                        t.type1 project,\n" +
            "                        0 actualamount,\n" +
            "                        0 nowmonthincome,\n" +
            "                        '0' budgetamount,\n" +
            "                        SUM(g.amount) actualamount1,\n" +
            "                        0 nowmonthincome1\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                   and to_char(g.done_date,'yyyy-mm-dd')&lt; =  to_char(trunc(sysdate,'mm'),'yyyy-mm-dd') \n" +
            "                                                                  AND to_char(g.done_date,'yyyy-mm-dd')&gt;= to_char(trunc(sysdate,'yyyy'),'yyyy-mm-dd') \n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
            "                    WHERE\n" +
            "                        t.type2 = #{type} " +
            "                        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
            "                        <if test='orgid != \"\"'> and b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    GROUP BY\n" +
            "                        t.type1,b.amount_goal\n" +
            "                    UNION ALL\n" +
            "                    SELECT\n" +
            "                        t.type1 project,\n" +
            "                        0,\n" +
            "                        0,\n" +
            "                        '0',0,\n" +
            "                        SUM(g.amount) nowmonthincome1\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                   and to_char(g.done_date,'yyyy-mm-dd')&lt; =  to_char(trunc(sysdate,'mm'),'yyyy-mm-dd')\n" +
            "                                                                  and to_char(g.done_date,'yyyy-mm-dd')&gt; =  to_char(trunc(add_months(sysdate,-1),'mm'),'yyyy-mm-dd') \n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
            "                    WHERE\n" +
            "                        t.type2 = #{type} " +
            "                        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
            "                        <if test='orgid != \"\"'> and b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    GROUP BY\n" +
            "                        t.type1\n" +
            "                   UNION ALL\n" +
            "                   SELECT\n" +
            "                        t.type1 project, SUM(g.amount) actualamount,0,\n" +
            "                        b.amount_goal budgetamount,\n" +
            "                        0,\n" +
            "                       \n" +
            "                        0 nowmonthincome\n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                  AND to_char(g.done_date, 'yyyy') = #{year}\n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
            "                    WHERE\n" +
            "                        t.type2 = #{type} " +
            "                        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
            "                        <if test='orgid != \"\"'> and b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    GROUP BY\n" +
            "                        t.type1,b.amount_goal\n" +
            "                    UNION ALL\n" +
            "                    SELECT\n" +
            "                        t.type1 project,0,SUM(g.amount) nowmonthincome,\n" +
            "                        '0',\n" +
            "                        0,0\n" +
            "                        \n" +
            "                    FROM\n" +
            "                        budget_type                  t\n" +
            "                        LEFT JOIN gross_monomer   g ON TRIM(t.id) = g.bus_type3\n" +
            "                                                                  AND to_char(g.done_date, 'yyyy-mm') = #{month}\n" +
            "                        LEFT JOIN budget_target                b ON TRIM(t.type1) = b.content\n" +
            "                    WHERE\n" +
            "                        t.type2 = #{type} " +
            "                        <if test='orgid == \"\"'> and b.orgid = '1001'  </if>" +
            "                        <if test='orgid != \"\"'> and b.orgid = #{orgid} and g.segment1 = #{orgid}  </if>\n" +
            "                    GROUP BY\n" +
            "                        t.type1\n" +
            "                   \n" +
            "                )\n" +
            "            GROUP BY\n" +
            "                project\n" +
            "            ORDER BY\n" +
            "                actualamount DESC" +
            "</script>")
    fun getIncomeDetail(year: String, month: String, type: String, orgid: String): List<Budget>


    @Select("SELECT\n" +
            "    m.project,\n" +
            "    m.actualamount,\n" +
            "    m.budgetamount,\n" +
            "    m.nowmonthincome\n" +
            "FROM\n" +
            "    budget_all_mx   m,\n" +
            "    budget_type     t\n" +
            "WHERE\n" +
            "    TRIM(t.type1) = m.project\n" +
            "    AND t.type2 = #{type}\n" +
            "GROUP BY\n" +
            "    m.project,\n" +
            "    m.actualamount,\n" +
            "    m.budgetamount,\n" +
            "    m.nowmonthincome")
    fun getAllInDetail(type: String): List<Budget>

    @Select("select * from BUDGET_ALL")
    fun getInAll(): List<Budget>

    @Select("<script>SELECT\n" +
            "    SUM(amount) actualAmount,\n" +
            "    to_char(g.done_date, 'mm') month\n" +
            "FROM\n" +
            "    gross_monomer   g,\n" +
            "    budget_type                  t\n" +
            "WHERE\n" +
            "    to_char(g.done_date, 'yyyy') = #{year}" +
            "    <if test='orgid != \"\"'> and g.segment1 = #{orgid}  </if>\n" +
            "    AND t.id = g.bus_type3\n" +
            "    AND t.type1 = #{project}\n" +
            "GROUP BY\n" +
            "    to_char(g.done_date, 'mm')</script>")
    fun getInDetailByMonth(year: String, project: String, orgid: String): List<Budget>

    @Select("<script>SELECT\n" +
            "    SUM(t.amount) actualAmount,\n" +
            "    d.week_month month\n" +
            "FROM\n" +
            "    gross_monomer   t,\n" +
            "    budget_type                  b,\n" +
            "    t_date                       d\n" +
            "WHERE\n" +
            "    <if test='orgid != \"\"'> t.segment1 LIKE concat(#{orgid}, '%') and  </if>\n" +
            "    to_char(t.done_date, 'yyyy-mm') = #{month}\n" +
            "    AND t.bus_type3 = b.id\n" +
            "    AND b.type1 = #{project}\n" +
            "    AND to_char(t.done_date, 'yyyy-mm-dd') = to_char(d.datevalue, 'yyyy-mm-dd')\n" +
            "GROUP BY\n" +
            "    d.week_month\n" +
            "ORDER BY\n" +
            "    SUM(t.amount) DESC</script>")
    fun getInDetailByWeek(month: String, project: String, orgid: String): List<Budget>

    @Select("<script>SELECT\n" +
            "    SUM(t.amount) actualAmount,\n" +
            "    d.week_month  month\n" +
            "FROM\n" +
            "    (\n" +
            "        SELECT\n" +
            "            pro_type3 bus_type3,\n" +
            "            substr(ordered_date, 0, 10) done_date,\n" +
            "            CASE\n" +
            "                WHEN pro_type3 IN (\n" +
            "                    '111005001',\n" +
            "                    '111001001',\n" +
            "                    '111001005'\n" +
            "                ) THEN\n" +
            "                    '金盾维护费'\n" +
            "                WHEN pro_type3 IN (\n" +
            "                    '110002001'\n" +
            "                ) THEN\n" +
            "                    '税控服务费'\n" +
            "                ELSE\n" +
            "                    '其他'\n" +
            "            END project,\n" +
            "            amount * 0.5 amount\n" +
            "        FROM\n" +
            "            cux_scjs_sales_order_details t\n" +
            "        WHERE\n" +
            "            <if test='orgid != \"\"'> order_type LIKE concat(#{orgid}, '%') and </if>\n" +
            "            order_type LIKE '%分摊%'\n" +
            "            AND ordered_date LIKE concat(#{month},'%')\n" +
            "            AND t.item IN (\n" +
            "                '2701T239901112',\n" +
            "                '2701T231001010',\n" +
            "                'H00060000000043',\n" +
            "                'H00090000000633',\n" +
            "                '2701T239901113',\n" +
            "                '2701T239901089',\n" +
            "                'H01290000000221',\n" +
            "                'H01290000000223'\n" +
            "            )\n" +
            "    ) t,\n" +
            "    budget_type   b,\n" +
            "    t_date        d\n" +
            "WHERE\n" +
            "    t.bus_type3 = b.id\n" +
            "    AND b.type1 = #{project}\n" +
            "    AND t.done_date = to_char(d.datevalue, 'yyyy-mm-dd')\n" +
            "GROUP BY\n" +
            "    d.week_month\n" +
            "ORDER BY\n" +
            "    month ASC</script>")
    fun getInServiceByWeek(month: String, project: String, orgid: String): List<Budget>

    @Select("<script>SELECT\n" +
            "    e.name project,\n" +
            "    SUM(t.amount) actualAmount\n" +
            "FROM\n" +
            "    cux_scjs_sales_order_details   t,\n" +
            "    sys_employee                   e,\n" +
            "    budget_type                    b\n" +
            "WHERE\n" +
            "    t.salesrep_number = to_char(e.engineers_no)\n" +
            "    <if test='orgid != \"\"'> AND t.order_type like concat(#{orgid}, '%')  </if>\n" +
            "    AND t.ordered_date like concat(#{month}, '%')\n" +
            "    AND t.pro_type3 = b.id\n" +
            "    AND b.type1 = #{project}\n" +
            "GROUP BY\n" +
            "    e.name\n" +
            "ORDER BY\n" +
            "    SUM(t.amount) DESC</script>")
    fun getIncomeByPerson(month: String, project: String, orgid: String): List<Budget>
    /**
     * 获取服务费
     */
    @Select("<script>select sum(actualAmount) actualAmount, sum(nowMonthIncome) nowMonthIncome from  (\n" +
            "         select sum(amount) actualAmount,0 nowMonthIncome from  gross_monomer where  bus_type3 is not null <if test='orgid != \"\"'> and segment1 = #{orgid}  </if>\n" +
            "         and to_char(done_date, 'yyyy') = #{year} \n" +
            "         and bus_type3 in('110002001','111001001' ,'111001005') \n" +
            "         and amount &lt;0\n" +
            "         union all\n" +
            "         select 0,sum(amount) nowMonthIncome from  gross_monomer where bus_type3 is not null <if test='orgid != \"\"'> and segment1 = #{orgid}  </if>\n" +
            "         and to_char(done_date, 'yyyy-mm') = #{month} \n" +
            "         and bus_type3 in('110002001','111001001' ,'111001005') \n" +
            "         and amount &lt;0\n" +
            "         )</script>")
    fun getService(year: String, month: String, orgid: String): Budget

    /**
     * 除服务费之外的毛利
     */
    @Select("<script>select sum(amount)\n" +
            "          from gross_monomer\n" +
            "         where bus_type3 is not null" +
            "         <if test='orgid != \"\"'> and segment1 = #{orgid}  </if>" +
            "         and (bus_type3 = '111001001' \n" +
            "         or bus_type3 = '110002001')\n" +
            "         and to_char(done_date, 'yyyy-mm') = #{month}</script>")
    fun getOldServiceIncome(month: String, orgid: String): BigDecimal?

    /**
     * 本月服务费
     */
    @Select("<script>select project, sum(d.amount) * 0.5 actualAmount \n" +
            " from (select item,\n" +
            "              case when pro_type3 in ('111005001' , '111001001','111001005') then '金盾维护费'\n" +
            "              when pro_type3 in ('110002001') then '税控服务费'\n" +
            "              else '其他' end project,\n" +
            "              amount\n" +
            "         from CUX_SCJS_SALES_ORDER_DETAILS t\n" +
            "        where <if test='orgid != \"\"'> order_type like concat(#{orgid}, '%') and  </if>\n" +
            "          order_type like '%分摊%'\n" +
            "          and ordered_date like concat(#{month}, '%')\n" +
            "          and  t.item in ('2701T239901112','2701T231001010', 'H00060000000043', 'H00090000000633',\n" +
            "          '2701T239901113','2701T239901089','H01290000000221', 'H01290000000223')) d\n" +
            "          group by project</script>")
    fun getServiceIncome(month: String, orgid: String): List<Budget>

    /**
     * 上月工资
     */
    @Select("<script> select sum(amount) wage" +
            "  from af_cost_qs_depa" +
            " where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if> " +
            "   period_name like concat(#{lastMonth}, '%')" +
            "   and (subtitle_name like '%工资%')</script>")
    fun getPreWage(lastMonth: String, orgid: String): BigDecimal?

    /**
     * 其他利润及税金附加
     */
    @Select("<script> select sum(amount1) actualAmount ,sum(amount2)nowMonthIncome  from (\n" +
            "select sum(amount)amount1,0 amount2  from af_cost_qs_depa \n" +
            "where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if>  ( segment3 like '5402%' \n" +
            "or segment3 like '5503%' or segment3 like '6201%'  )\n" +
            "  and period_name like concat(#{year}, '%')\n" +
            "  union all \n" +
            "  select 0 amount1, sum(amount)amount2 from af_cost_qs_depa \n" +
            "where  <if test='orgid != \"\"'> segment1 = #{orgid} and  </if> ( segment3 like '5402%' \n" +
            "or segment3 like '5503%' or segment3 like '6201%'  )\n" +
            "  and period_name like concat(#{month}, '%')\n" +
            " )</script>")
    fun getOtherCost(year: String, month: String, orgid: String): Budget

    /**
     * 累计工资
     */
    @Select("<script> select sum(amount) wage" +
            "  from af_cost_qs_depa" +
            " where <if test='orgid != \"\"'> segment1 = #{orgid} and  </if> "+
            "   period_name like concat(#{year}, '%')" +
            "   and (subtitle_name like '%工资%') </script>")
    fun getAllWage(year: String, orgid: String): BigDecimal?

    @Select("<script>" +
            "SELECT\n" +
            "    b.type1 content,\n" +
            "    b.type2,\n" +
            "    SUM(g.amount) amount_goal,\n" +
            "    to_char(g.done_date, 'mm') month\n" +
            "FROM\n" +
            "    budget_target           t,\n" +
            "    budget_type             b,\n" +
            "    gross_monomer   g\n" +
            "WHERE\n" +
            "    t.content = b.type1\n" +
            "    <if test='orgid == \"\"'> and t.orgid = '1001'  </if>" +
            "    <if test='orgid != \"\"'>" +
            "       and t.orgid = #{orgid} " +
            "       and g.segment1 = #{orgid}" +
            "    </if>\n" +
            "    AND b.id = g.bus_type3\n" +
            "    AND to_char(g.done_date, 'yyyy') = #{year}" +
            "    <if test='type != \"\"'> and t.type = #{type}</if>\n" +
            "GROUP BY\n" +
            "    b.type1,\n" +
            "    b.type2,\n" +
            "    to_char(g.done_date, 'mm')\n" +
            "    order by b.type1 desc" +
            "</script>")
    fun getResultDetail(year: String, orgid: String, type: String): List<BudgetTarget>

    @Select("<script>" +
            "SELECT\n" +
            "    b.type2 ,\n" +
            "    SUM(g.amount) amount_goal,\n" +
            "    to_char(g.done_date, 'mm') month\n" +
            "FROM\n" +
            "    budget_target           t,\n" +
            "    budget_type             b,\n" +
            "    gross_monomer   g\n" +
            "WHERE\n" +
            "    t.content = b.type1\n" +
            "    <if test='orgid == \"\"'> and t.orgid = '1001'  </if>" +
            "    <if test='orgid != \"\"'>" +
            "       and t.orgid = #{orgid} " +
            "       and g.segment1 = #{orgid}" +
            "    </if>\n" +
            "    AND b.id = g.bus_type3\n" +
            "    AND to_char(g.done_date, 'yyyy') = #{year}" +
            "GROUP BY\n" +
            "    b.type2,\n" +
            "    to_char(g.done_date, 'mm')\n" +
            "    order by b.type2 desc" +
            "</script>")
    fun getResultAll(year: String, orgid: String): List<BudgetTarget>
}
